import java.sql.Connection
import java.sql.DriverManager
import java.sql.ResultSet
import java.sql.Statement

/**
 * 连接h2数据库，并且创建数据库表，插入数据，查询数据
 */
object DbClient {


    // 数据库连接URL
    private const val JDBC_URL = "jdbc:h2:file:C:/data/sample1"

    // 连接数据库的用户名
    private const val USER = "sa"

    // 连接数据库的密码
    private const val PASSWORD = ""

    // 连接数据库的驱动类(H2数据库提供)
    private const val DRIVER_CLASS = "org.h2.Driver"
    @Throws(Exception::class)
    fun createdb() {
        // 加载H2数据库驱动
        Class.forName(DRIVER_CLASS)
        // 根据连接URL，用户名，密码获取数据库连接
        val conn: Connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD)
        val stmt: Statement = conn.createStatement()
        // 如果存在USERS表就先删除USERS表
        stmt.execute("DROP TABLE IF EXISTS USERS")
        // 创建users表
        stmt.execute(
            "create table users("
                    + "    id int primary key,"
                    + "    name varchar(40),"
                    + "    password varchar(40))"
        )
        // 新增
        stmt.executeUpdate("INSERT INTO users VALUES(1,'张三','12')")
        stmt.executeUpdate("INSERT INTO users VALUES(2,'李四','34')")
        stmt.executeUpdate("INSERT INTO users VALUES(3,'王五','56')")
        stmt.executeUpdate("INSERT INTO users VALUES(4,'麻六','78')")
        stmt.executeUpdate("INSERT INTO users VALUES(5,'邹七','90')")
        // 删除
        stmt.executeUpdate("DELETE FROM users WHERE name='张三'")
        // 修改
        stmt.executeUpdate("UPDATE users SET name='李二' WHERE name='李四'")
        // 查询
        val rs: ResultSet = stmt.executeQuery("SELECT * FROM users")
        // 遍历结果集
        while (rs.next()) {
            println(rs.getString("id") + "," + rs.getString("name") + "," + rs.getString("password"))
        }
        // 释放资源
        stmt.close()
        // 关闭连接
        conn.close()
    }
}

fun main() {
    DbClient.createdb()
}
